package com.xmy.cultivate.mapper;

import com.xmy.cultivate.entity.OrderDetail;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 订单报名信息 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-03-20
 */
public interface OrderDetailMapper extends BaseMapper<OrderDetail> {

    @Select("select a.id,a.year_part as yearPart,a.is_new_student as isNewStudent,c.quarter_num as quarterNum from order_detail as a left join order_apply as b on a.order_id = b.id left join course as c on a.course_id = c.id where a.school_id = #{schoolId} and a.student_id = #{studentId} and c.subjects_id = #{subjectsId} and b.status <> 2 and a.deleted = 0 and ((a.year_part<#{newYeartPart}) or (a.year_part=#{newYeartPart} and a.quarter_num<=#{newNum})) and a.reading_status<>3 and a.lesson_type=${lessonType} ORDER BY a.created_at desc limit 0,1 ")
    public  abstract Map<String,Object> getApplyCourseForSubjects(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("newYeartPart") Integer newYeartPart, @Param("newNum") Integer newNum, @Param("lessonType")Integer lessonType);


    /**
     * 获取所报读科目的剩余课时
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @return
     */
    @Select({
            "<script>",
            "select ifnull(sum(residue_course_count),0) as residue_course_count from order_detail where deleted=0 and status &lt;&gt; 2 ",
            "<when test='schoolId!=null'>",
            "AND school_id = ${schoolId}",
            "</when>",
            "and student_id=#{studentId} and subjects_id=#{subjectsId} and residue_course_count>0 and year_and_quarter>=${yearAndQuarter} and lesson_type=${lessonType}",
            "</script>"
    })
    public abstract Integer getResidueCourseCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);


    /**
     * 获取科目的剩余课次
     * @param schoolId
     * @param studentId
     * @param subjectsId
     * @param yearAndQuarter
     * @return
     */
    @Select({"<script>",
            "select IFNULL(sum(a.residue_course_count/3),0) as residueCourseCount  from order_detail as a left join grade as b on a.grade_id = b.id where 1=1 ",
            "<when test='schoolId!=null'>",
                "and a.school_id= #{schoolId} ",
            "</when>",
                "and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.status &lt;&gt; 2 and a.year_and_quarter &gt;= #{yearAndQuarter} and a.lesson_type=${lessonType} and a.deleted = 0 ",
            "</script>"})
    public abstract Integer getResidueCourseCount(@Param("schoolId") Long schoolId, @Param("studentId") Long studentId, @Param("subjectsId") Long subjectsId, @Param("yearAndQuarter") Integer yearAndQuarter, @Param("lessonType") Integer lessonType);


    @Select({"<script>",
            "SELECT",
            "school_id,subjects_id,year_class_id,lesson_type,student_id,IFNULL(SUM(residue_course_count),0) as residue_course_count,created_id",
                "FROM",
            "order_detail",
                "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND deleted = 0",
            "AND residue_course_count&gt;0",
            "AND lesson_type = ${lessonType}",
            "<when test='schoolId!=null'>",
                "AND school_id = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null'>",
                "AND subjects_id = ${subjectsId}",
            "</when>",
            "<when test='studentId!=null'>",
                "AND student_id = ${studentId}",
            "</when>",
            "GROUP BY school_id,student_id,subjects_id,lesson_type",
            "ORDER BY school_id,subjects_id,lesson_type,student_id",
            "</script>"})
    public abstract List<OrderDetail> getStudentOrderDetail(@Param("yearPart") Integer yearPart, @Param("quarterNum") Integer quarterNum, @Param("schoolId") Long schoolId, @Param("subjectsId") Long subjectsId, @Param("lessonType") Integer lessonType, @Param("studentId") Long studentId);


    @Select({"<script>",
            "SELECT",
            "*",
            "FROM",
            "order_detail",
            "WHERE",
            "year_part = ${yearPart} ",
            "AND quarter_num = ${quarterNum} ",
            "AND `status` &lt;&gt; 2 ",
            "AND deleted = 0",
            "AND lesson_type = ${lessonType}",
            "<when test='schoolId!=null'>",
            "AND school_id = ${schoolId}",
            "</when>",
            "<when test='subjectsId!=null'>",
            "AND subjects_id = ${subjectsId}",
            "</when>",
            "<when test='studentId!=null'>",
            "AND student_id = ${studentId}",
            "</when>",
            "limit 1",
            "</script>"})
    public abstract OrderDetail getStudetnOrderDetailForLessonType(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType,@Param("studentId") Long studentId);

    @Update("UPDATE order_detail set residue_course_count = ${residueCourseCount} where year_part = ${yearPart} and quarter_num = ${quarterNum} and school_id = ${schoolId} and subjects_id  = ${subjectsId} and student_id = ${studentId} and lesson_type = ${lessonType}  and deleted = 0")
    public abstract Integer updateResidueCourse(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("schoolId") Long schoolId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType,@Param("studentId") Long studentId,@Param("residueCourseCount") Integer residueCourseCount);


    @Select({"<script>",
            "SELECT",
                    "SUM( residue_course_count + use_course_count ) AS all_course_count,",
                    "SUM( residue_price + use_price ) AS price,",
                    "SUM( use_course_count ) AS use_course_count,",
                    "SUM( use_price ) AS use_price,",
                    "SUM( residue_course_count ) AS residue_course_count,",
                    "SUM( residue_price ) AS residue_price ",
                    "FROM",
                    "order_detail ",
                    "WHERE",
                    "school_id = ${schoolId} ",
                    "AND subjects_id = ${subjectsId} ",
                    "AND student_id = ${studentId} ",
                    "AND year_part = ${yearPart} ",
                    "AND quarter_num = ${quarterNum} ",
                    "AND lesson_type = ${lessonType} ",
                    "AND `status` &lt;&gt; 2 ",
                    "AND deleted = 0 ",
                    "GROUP BY",
                    "student_id",
            "</script>"})
    public abstract OrderDetail getCourseHouseAndPrice(@Param("yearPart") Integer yearPart,
                                                       @Param("quarterNum") Integer quarterNum,
                                                       @Param("schoolId") Long schoolId,
                                                       @Param("subjectsId") Long subjectsId,
                                                       @Param("lessonType") Integer lessonType,
                                                       @Param("studentId") Long studentId);

    @Select("select * from order_detail where year_part = ${yearPart} and quarter_num = ${quarterNum} and lesson_type =${lessonType} and student_id = ${studentId} and subjects_id =${subjectsId} and deleted = 0 ORDER BY id desc limit 1")
    public abstract OrderDetail getLastOneForStudent(@Param("yearPart") Integer yearPart,@Param("quarterNum") Integer quarterNum,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("lessonType") Integer lessonType);

    @Select({"<script>",
            "select IFNULL(sum((a.all_course_count-a.out_house-a.turn_out_house)/3),0) as allCourseCount  from order_detail as a left join grade as b on a.grade_id = b.id where 1=1 ",
            "<when test='schoolId!=null'>",
                "and a.school_id= #{schoolId} ",
            "</when>",
            "and a.student_id = #{studentId} and a.subjects_id = #{subjectsId} and a.status  &lt;&gt; 2 and a.year_and_quarter &gt;= #{yearAndQuarter} and a.lesson_type=${lessonType} and a.deleted = 0 ",
            "</script>"})
    public abstract Integer getAllCourseCountForSubjects(@Param("schoolId") Long schoolId,@Param("studentId") Long studentId,@Param("subjectsId") Long subjectsId,@Param("yearAndQuarter") Integer yearAndQuarter,@Param("lessonType") Integer lessonType);


}
